﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Collections;

namespace WebService
{
    public class dbConnection
    {
        SqlConnection con;

        public dbConnection()
        {
            //kết nối
            string strcon = ConfigurationManager.ConnectionStrings["GPSTravelConnect"].ToString();
            con = new SqlConnection(strcon);
        }

        public DataTable GetDataTable(string query)
        {
            DataTable tb = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(query,con);
            da.SelectCommand.CommandTimeout = 360;
            da.Fill(tb);
            return tb;
        }

        public int ExecuteQuery(string query,IDictionary<string,string> d,bool isStored)
        {
            SqlCommand cmd=new SqlCommand();
            cmd.CommandText = query;
            if (isStored)
                cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = con;
            cmd.CommandTimeout = 360;
            if (d != null)
            {
                IDictionaryEnumerator en = (IDictionaryEnumerator)d.GetEnumerator();
                while (en.MoveNext())
                {
                    SqlParameter param = new SqlParameter();
                    param.ParameterName = en.Key.ToString();
                    param.Value = en.Value.ToString();
                    cmd.Parameters.Add(param);
                }
            }
            con.Open();
            int affectedrow = cmd.ExecuteNonQuery();
            con.Close();
            return affectedrow;
        }


        public int ExecuteScalar(string query, IDictionary<string, string> d, bool isStored)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = query;
            if (isStored)
                cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = con;
            cmd.CommandTimeout = 360;
            if (d != null)
            {
                IDictionaryEnumerator en = (IDictionaryEnumerator)d.GetEnumerator();
                while (en.MoveNext())
                {
                    SqlParameter param = new SqlParameter();
                    param.ParameterName = en.Key.ToString();
                    param.Value = en.Value.ToString();
                    cmd.Parameters.Add(param);
                }
            }
            con.Open();
            int affectedrow = Convert.ToInt32(cmd.ExecuteScalar());
            con.Close();
            return affectedrow;
        }





    }
}